package com.john.market.classes;

import java.util.UUID;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Handler;

import com.john.market.constants.DBConstants;
import com.john.market.constants.ProductType;
import com.john.market.interfaces.Storage;

/**
 * Database storage
 *
 */
public class DBStorage extends Storage {
	// Database
	private SQLiteDatabase mDatabase = null;
	
	// Handler for threads
	private Handler mHandler = new Handler();
		
	/**
	 * Helper for the database
	 *
	 */
	private class DBHelper extends  SQLiteOpenHelper{
		/**
		 * Creates new database helper
		 * 
		 * @param context
		 * Context
		 * 
		 */
		public DBHelper(Context context) {
			super(context, DBConstants.DB_NAME, null, DBConstants.DB_VERSION);
			
		}

		@Override
		public void onCreate(SQLiteDatabase database) {
			final String CREATE =
				"CREATE TABLE IF NOT EXISTS " + DBConstants.TABLE_NAME + " (" + 
					DBConstants.COLUMN_KEY + " INT PRIMARY KEY, " +
					DBConstants.COLUMN_ID + " TEXT NOT NULL, " +
					DBConstants.COLUMN_TITLE + " TEXT NOT NULL, " +
					DBConstants.COLUMN_TYPE + " INT NOT NULL, " +
					DBConstants.COLUMN_AMOUNT + " INT NOT NULL, " +
					DBConstants.COLUMN_PRICE + " NUMBER NOT NULL" +
				")";
			
			// Creating table in the new database
			try {
				database.execSQL(CREATE);
			} catch (SQLException e) {
				return;
			}
		}

		@Override
		public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
			if (oldVersion != newVersion) {
				final String DROP = "DROP TABLE IF EXISTS " + DBConstants.TABLE_NAME;
				
				// Dropping existing table 
				try {
					database.execSQL(DROP);
				} catch (SQLException e) {
					return;
				}
				
				// Create new database
				onCreate(database);
			}
			
		}
		
	};
	
	/**
	 * Creates new database storage object
	 * 
	 * @param context
	 * Application context
	 * 
	 * @throws SQLException
	 * Throw when database can not be accessed
	 * 
	 */
	public DBStorage(Context context) throws SQLException {
		// Getting writable database with the aid of DBHelper 
		DBHelper helper = new DBHelper(context);
		this.mDatabase = helper.getWritableDatabase();
		
	}
	
	@Override
	public void refresh() {
		Runnable runnable = new Runnable() {
			private Product mProduct = null;
			
			@Override
			public void run() {
				final String SELECT_ALL =
					"SELECT * FROM " + DBConstants.TABLE_NAME +
					" ORDER BY " + DBConstants.COLUMN_KEY;
				
				Cursor cursor = null;
				
				// Getting all products
				try {
					cursor = mDatabase.rawQuery(SELECT_ALL, null);
				} catch (SQLException e) {
					return;
				}
				
				// Check if cursor is empty
				if (!cursor.moveToFirst()) return;
				
				int index = 0;
				
				// Bypassing all raws in the cursor
				do {
					try {
						Thread.sleep(500);
					} catch (InterruptedException e) {
						e.printStackTrace();
					}
					
					// Getting id of the product
					index = cursor.getColumnIndex(DBConstants.COLUMN_ID);
					String id = cursor.getString(index);
					
					// Getting title of the product
					index = cursor.getColumnIndex(DBConstants.COLUMN_TITLE);
					String title = cursor.getString(index);
					
					// Getting type of the product
					index = cursor.getColumnIndex(DBConstants.COLUMN_TYPE);
					int ordinal = cursor.getInt(index);
					
					// Getting amount of the product
					index = cursor.getColumnIndex(DBConstants.COLUMN_AMOUNT);
					int amount = cursor.getInt(index);
					
					// Getting price of the product
					index = cursor.getColumnIndex(DBConstants.COLUMN_PRICE);
					float price = cursor.getFloat(index);
					
					// Creating a new product with received data
					Product product = new Product();
					product.setId(UUID.fromString(id));
					product.setTitle(title);
					product.setType(ProductType.values()[ordinal]);
					product.setAmount(amount);
					product.setPrice(price);
					
					this.mProduct = product;
					
					mHandler.post(new Runnable() {
						@Override
						public void run() {
							// Notify listener
							mListener.refresh(mProduct);
						}
					});
					
				} while(cursor.moveToNext());
				
			}
			
		};
		
		new Thread(runnable).start();
		
	}
	
	@Override
	public int buyProduct(Product product, int count) {
		// Getting product ID
		String id = product.getId().toString();
				
		final String SELECT =
			"SELECT " + DBConstants.COLUMN_AMOUNT + " FROM " + DBConstants.TABLE_NAME +
			" WHERE " + DBConstants.COLUMN_ID + " = " + "'" + id + "'";
			
		Cursor cursor = null;
		
		// Getting the product
		try {
			cursor = mDatabase.rawQuery(SELECT, null);
		} catch (SQLException e) {
			return 0;
		}
		
		// Check if cursor is empty
		if (!cursor.moveToFirst()) return 0;
		
		// Getting amount of the product
		int index = cursor.getColumnIndex(DBConstants.COLUMN_AMOUNT);
		int oldAmount = cursor.getInt(index);
		
		// Calculating new amount
		int amount = oldAmount - count;
		
		if (amount <= 0) {
			removeProduct(product.getId());
			return amount;
		}
			
		final String UPDATE =
			"UPDATE " + DBConstants.TABLE_NAME + " SET " + 
				DBConstants.COLUMN_AMOUNT + " = " + amount + 
			" WHERE " + DBConstants.COLUMN_ID + " = " + "'" + id + "'";
			
		// Updating product if exists 
		try {
			this.mDatabase.execSQL(UPDATE);
		} catch (SQLException e) {
			return oldAmount;
		}
		
		return amount;
	}
	
	@Override
	public boolean addProduct(Product product) {
		// Getting product data
		String id = product.getId().toString();
		String title = product.getTitle();
		int type = product.getType().ordinal();
		int amount = product.getAmount();
		float price = product.getPrice();
		
		final String SELECT_SAME =
			"SELECT " + DBConstants.COLUMN_ID +
			" FROM " + DBConstants.TABLE_NAME +
			" WHERE " +
				DBConstants.COLUMN_TITLE + " = " + "'" + title + "'" + " AND " +
				DBConstants.COLUMN_TYPE + " = " + type;
		
		Cursor cursor = null;
		
		// Getting products with the same titles and types as in the product being added
		try {
			cursor = this.mDatabase.rawQuery(SELECT_SAME, null);
		} catch (SQLException e) {
			return false;
		}
		
		// Update or insert product
		if (cursor.moveToFirst()) {
			int index = cursor.getColumnIndex(DBConstants.COLUMN_ID);
			String oldId = cursor.getString(index);
			
			final String UPDATE =
				"UPDATE " + DBConstants.TABLE_NAME + " SET " +
						DBConstants.COLUMN_AMOUNT + " = " + amount + ", " +
						DBConstants.COLUMN_PRICE + " = " + price +
				" WHERE " + DBConstants.COLUMN_ID + " = " + "'" + oldId + "'";
			
			// Updating existing product
			try {
				this.mDatabase.execSQL(UPDATE);
			} catch (SQLException e) {
				return false;
			}
			
			// Refresh product ID
			product.setId(UUID.fromString(oldId));
			
		}
		else {
			final String INSERT =
				"INSERT INTO " + DBConstants.TABLE_NAME + " (" +
						DBConstants.COLUMN_ID + ", " +
						DBConstants.COLUMN_TITLE + ", " +
						DBConstants.COLUMN_TYPE + ", " +
						DBConstants.COLUMN_AMOUNT + ", " +
						DBConstants.COLUMN_PRICE +
				") VALUES (" +
				 		"'" + id + "'" + ", " +
						"'" + title + "'" + ", " +
						type + ", " +
						amount + ", " +
						price + 
				")";
			
			// Adding new product
			try {
				this.mDatabase.execSQL(INSERT);
			} catch (SQLException e) {
				return false;
			}
		}
		
		return true;
	}

	@Override
	public boolean updateProduct(Product product) {
		// Getting product data
		String id = product.getId().toString();
		String title = product.getTitle();
		int type = product.getType().ordinal();
		int amount = product.getAmount();
		float price = product.getPrice();
		
		final String UPDATE =
			"UPDATE " + DBConstants.TABLE_NAME + " SET " + 
				DBConstants.COLUMN_TITLE + " = " + "'" + title + "'" + ", " +
				DBConstants.COLUMN_TYPE + " = " + type + ", " +
				DBConstants.COLUMN_AMOUNT + " = " + amount + ", " +
				DBConstants.COLUMN_PRICE + " = " + price +
			" WHERE " + DBConstants.COLUMN_ID + " = " + "'" + id + "'";
		
		// Updating product if exists 
		try {
			this.mDatabase.execSQL(UPDATE);
		} catch (SQLException e) {
			return false;
		}
		
		return true;
	}

	@Override
	public boolean removeProduct(UUID id) {
		final String DELETE =
			"DELETE FROM " + DBConstants.TABLE_NAME +
			" WHERE " + DBConstants.COLUMN_ID + " = " + "'" + id + "'";
		
		// Updating product if exists
		try {
			this.mDatabase.execSQL(DELETE);
		} catch (SQLException e) {
			return false;
		}
		
		return true;	
	}
	
}
